DROP TABLE if EXISTS categorys, users, locations, events, requests, compilations, events_compilations, comments CASCADE;

CREATE TABLE if NOT EXISTS categorys (
    id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    CONSTRAINT UQ_CATEGORY_NAME UNIQUE (name)
);

CREATE TABLE if NOT EXISTS users (
    id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY,
    name VARCHAR(250) NOT NULL,
    email VARCHAR(254) NOT NULL,
    CONSTRAINT UQ_USER_EMAIL UNIQUE (email)
);

CREATE TABLE if NOT EXISTS locations (
    id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY,
    lat REAL NOT NULL,
    lon REAL NOT NULL
);

CREATE TABLE if NOT EXISTS events (
    id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY,
    annotation VARCHAR(2000) NOT NULL,
    category_id INT REFERENCES categorys (id) ON DELETE CASCADE,
    confirmed_requests INT,
    created_on TIMESTAMP NOT NULL,
    description VARCHAR(7000) NOT NULL,
    event_date TIMESTAMP NOT NULL,
    initiator_id INT REFERENCES users (id) ON DELETE CASCADE,
    location_id INT REFERENCES locations (id) ON DELETE CASCADE,
    paid Boolean NOT NULL,
    participant_limit INT NOT NULL,
    published_on TIMESTAMP,
    request_moderation Boolean NOT NULL,
    state VARCHAR(255) NOT NULL,
    title VARCHAR(120) NOT NULL,
    views INT
);

CREATE TABLE if NOT EXISTS requests (
    id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY,
    event_id INT REFERENCES events (id) ON DELETE CASCADE,
    requester_id INT REFERENCES users (id) ON DELETE CASCADE,
    created TIMESTAMP NOT NULL,
    status VARCHAR(255) NOT NULL
);

CREATE TABLE if NOT EXISTS compilations (
    id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY,
    event_id INT REFERENCES events (id) ON DELETE CASCADE,
    pinned Boolean NOT NULL,
    title VARCHAR(50) NOT NULL
);

CREATE TABLE IF NOT EXISTS events_compilations
(
    event_id INT REFERENCES events (id) ON DELETE CASCADE,
    compilation_id INT REFERENCES compilations (id) ON DELETE CASCADE,
    PRIMARY KEY (event_id, compilation_id),
    CONSTRAINT COMPILATIONS_UNIQUE UNIQUE (event_id, compilation_id)
);

CREATE TABLE IF NOT EXISTS comments (
	id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY,
	text VARCHAR(2000) NOT NULL,
	event_id INT REFERENCES events (id),
	author_id INT REFERENCES users (id),
	created TIMESTAMP NOT NULL
);